package com.hbfk.util;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author tyb 2016-5-17上午10:22:15 导入 导出excel
 */
public class ImportOrExportExcelUtil {
	/**
	 * @author tyb 2016-5-17上午10:29:32 导出 fileUrl:是否指定目录 如浏览器弹出下载框需把此参数设置为空
	 */
	public static void exportExcel(String[] titles, List<Object[]> objList1, ServletOutputStream outputStream, String fileUrl) {
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 数据查过五万添加一个sheet页
		int pageSize = 50000;
		Integer sheetNumber = objList1.size() % pageSize == 0 ? objList1.size() / pageSize : objList1.size() / pageSize + 1;
		int sizeNum = 0;
		FileOutputStream stream = null;
		for (int k = 0; k < sheetNumber; k++) {
			Integer listNumber = (k + 1) * pageSize > objList1.size() ? objList1.size() : (k + 1) * pageSize;
			// 在workbook中添加一个sheet,对应Excel文件中的sheet
			XSSFSheet sheet = workBook.createSheet("Sheet" + (k + 1));
			ExportUtil exportUtil = new ExportUtil(workBook, sheet);
			XSSFCellStyle headStyle = exportUtil.getHeadStyle();
			XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
			// 构建表头
			XSSFRow headRow = sheet.createRow(0);
			headRow.setHeight((short) 600);
			XSSFCell cell = null;
			for (int i = 0; i < titles.length; i++) {
				cell = headRow.createCell(i);
				cell.setCellStyle(headStyle);
				cell.setCellValue(titles[i]);
				sheet.autoSizeColumn(i, true);
			}
			int jj = 1;
			// 构建表体数据
			if (objList1 != null && objList1.size() > 0) {
				for (int j = sizeNum; j < listNumber; j++) {
					XSSFRow bodyRow = sheet.createRow(jj);
					Object[] obj = objList1.get(j);
					for (int i = 0; i < obj.length; i++) {
						cell = bodyRow.createCell(i);
						cell.setCellStyle(bodyStyle);
						cell.setCellValue(obj[i] == null ? "" : obj[i].toString());
						//sheet.autoSizeColumn(i, true); // 设置单元格宽度自适应
					}
					jj++;
					sizeNum++;
				}
			}
		}
		try {
			if (StringUtils.isNotBlank(fileUrl)) {
				File file = new File(fileUrl);
				file.createNewFile();
				stream = FileUtils.openOutputStream(file);
				workBook.write(stream);
			} else {
				workBook.write(outputStream);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (StringUtils.isNotBlank(fileUrl)) {
					stream.flush();
					stream.close();
				} else {
					outputStream.flush();
					outputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @author tyb 2016-5-17上午10:29:43 导入
	 */
	public static void importExcel(String url) throws IOException {
		XSSFWorkbook workBook = new XSSFWorkbook(FileUtils.openInputStream(new File(url)));
		XSSFSheet sheet = workBook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		for (int i = 1; i <= lastRowNum; i++) {
			XSSFRow row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			for (int k = 0; k < lastCellNum; k++) {
				XSSFCell cell = row.getCell(k);
				String value = cell.getStringCellValue();
				System.out.print("   " + value);
			}
			System.out.println();
		}
	}
}